const express = require('express');
const router = express.Router();
const mysql = require('mysql');
const bcrypt = require('bcrypt');
const constant = require('../data/constant');
const auth = require('../middleware/auth');

// 接口：获取树形结构
// 参数：fid（文件夹id）
// 返回：以该文件夹作为上级文件夹的下一级文件夹
router.get('/structure', (req,res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 查找文件夹
            let sql = '';
            let sqlParams = [];
            if(req.query.type == 1){
                // 项目
                sql = "SELECT * FROM folder where lastlevel = ? and project = ?;"
                sqlParams = [
                    req.query.fid,
                    req.query.project_id,
                ];
            }else{
                // 资产库
                sql = "SELECT * FROM folder where lastlevel = ? and project_tag = ?;"
                sqlParams = [
                    req.query.fid,
                    false
                ];
            }
            // console.log(sql,sqlParams);
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.length>0 ){
                        res.send({
                            res:{
                                "ok":1,
                                "data":res_data
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                "ok":0,
                                "msg":"该目录已经是最下层目录"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    })
});

// 接口：获取下级文件夹和文件
// 参数：fid（文件夹id）
// 返回：以该文件夹作为上级文件夹的下一级文件夹和文件
router.get('/enter',auth, (req,res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 查找文件夹和文件
            let sql = '';
            let sqlParams = [];
            if(req.query.type == 1){
                // 项目
                sql = "SELECT * FROM folder where lastlevel=? AND project=?;SELECT * FROM files where lastlevel=? AND project=?;"
                sqlParams = [
                    req.query.fid,
                    req.query.project_id,
                    req.query.fid,
                    req.query.project_id
                ];
            }else{
                // 资产库
                sql = "SELECT * FROM folder where lastlevel=? AND project_tag=?;SELECT * FROM files where lastlevel=? AND project is ? AND status=?;"
                sqlParams = [
                    req.query.fid,
                    false,
                    req.query.fid,
                    null,
                    3
                ];
            }
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data.length>0 ){
                        res.send({
                            res:{
                                "ok":1,
                                "data":res_data
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                "ok":0,
                                "msg":"该文件夹下没有子文件夹"
                            }
                        });
                        return 0;
                    }
                }
            });
            connection.release();
        }
    })
});

// 删除
router.post('/delete',auth, (req,res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 查找文件夹和文件
            let sql = '';
            let sqlParams = [];
            if(req.body.type == 'folder'){
                // 删除文件夹
                sql = "delete from folder where fid = ?;";
                sqlParams = [
                    req.body.id
                ];
            }else{
                // 删除文件
                sql = "delete from files where file_id = ?;"
                sqlParams = [
                    req.body.id
                ];
            }
            console.log(sql,sqlParams);
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    console.log(res_data);
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                "ok":1,
                                "msg":"删除成功！"
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                "ok":0,
                                "msg":"删除失败！"
                            }
                        });
                        return 0;
                    }
                }
            });
            connection.release();
        }
    })
});

// 增加课程
router.post('/add_course',(req,res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 先确认创建课程文件夹
            let sql = "INSERT INTO folder(name,lastlevel,project_tag,project) values(?,?,?,?);"
            let sqlParams = [
                req.body.name,
                req.body.fid,
                false,
                null
            ]
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                    connection.end();
                }else{
                    res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    if(res_data.affectedRows == 1){
                        // 创建课程子文件夹
                        let fid = res_data.insertId;
                        sql = "INSERT INTO folder(name,lastlevel,project_tag,project) values('教案讲稿',?,false,null);"+
                        "INSERT INTO folder(name,lastlevel,project_tag,project) values('试卷作业',?,false,null);"+
                        "INSERT INTO folder(name,lastlevel,project_tag,project) values('音视频资源',?,false,null);"+
                        "INSERT INTO folder(name,lastlevel,project_tag,project) values('多媒体课件',?,false,null);"+
                        "INSERT INTO folder(name,lastlevel,project_tag,project) values('电子出版物',?,false,null);"+
                        "INSERT INTO folder(name,lastlevel,project_tag,project) values('专题网站',?,false,null);"+
                        "INSERT INTO folder(name,lastlevel,project_tag,project) values('电子案例',?,false,null);"+
                        "INSERT INTO folder(name,lastlevel,project_tag,project) values('科研文献',?,false,null);"+
                        "INSERT INTO folder(name,lastlevel,project_tag,project) values('项目源码',?,false,null);";
                        sqlParams = [
                            fid,fid,fid,fid,fid,fid,fid,fid,fid
                        ]
                        connection.query(sql,sqlParams,(err,data) => {
                            if(err){
                                console.log(err);
                                res.send({
                                    'res':{
                                        'ok': 0,
                                        'msg':'数据库连接失败'
                                    }
                                });
                            }else{
                                res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                                res_data = JSON.parse(res_data);//把results字符串转为json对象
                                if(res_data.length == 9){
                                    // 遍历
                                    let success_tag = true;
                                    for(let i in res_data){
                                        if(res_data[i].affectedRows !== 1){
                                            success_tag = false;
                                        }
                                    }
                                    if(success_tag){
                                        res.send({
                                            'res':{
                                                'ok': 1,
                                                'msg':'新建课程成功！'
                                            }
                                        });
                                    }else{
                                        res.send({
                                            'res':{
                                                'ok': 0,
                                                'msg':'新建课程失败！'
                                            }
                                        });
                                    }

                                }else{
                                    res.send({
                                        'res':{
                                            'ok': 0,
                                            'msg':'新建课程失败！错误可能在于新建课程子文件夹出错'
                                        }
                                    });
                                }
                            }
                            connection.release();
                        });
                    }else{
                        res.send({
                            'res':{
                                'ok': 0,
                                'msg':'新建课程失败！错误可能在于新建课程父文件夹出错'
                            }
                        });
                    }
                }
            });
        }
    })
})
// 新建文件夹的接口
router.post('/add_folder',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "INSERT INTO folder(name,lastlevel,project_tag,project) values(?,?,?,?)"
            let  sqlParams = [
                req.body.name,
                req.body.lastlevel,
                false,
                null
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.affectedRows == 1){
                        res.send({
                            res:{
                                ok:1,
                                msg:"添加文件夹成功！"
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});

// 获取文件所在位置的接口
router.post('/get_location',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true, // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            let  sql = "select location_array from files where file_id=?;"
            let  sqlParams = [
                req.body.file_id,
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    if(res_data.length == 1){
                        let location_array = res_data[0].location_array.split(',');
                        let sql_array = [];
                        for(let i in location_array){
                            sql_array.push('select name from folder where fid = '+location_array[i]+';');
                        }
                        let sql_inner = sql_array.join('');
                        // console.log(sql_inner);
                        connection.query(sql_inner,async (err,data) => {
                            if(err){
                                console.log(err);
                                res.statusCode = 500;
                                res.send({
                                    'res':{
                                        'ok': 0,
                                        'msg':'数据库连接失败'
                                    }
                                });
                            }else{
                                let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                                res_data = JSON.parse(res_data);//把results字符串转为json对象
                                // console.log(res_data);
                                let result = [];
                                if(res_data.length >= 1){
                                    for(let i = 0;i < res_data.length;i++){
                                        result.push(res_data[i][0].name);
                                    }
                                    res.send({
                                        res:{
                                            ok:1,
                                            msg:"查找文件位置成功！",
                                            data:result
                                        }
                                    })
                                }else{
                                    res.send({
                                        res:{
                                            ok:0,
                                            msg:"没找到该文件的位置！"
                                        }
                                    })
                                }
                            }
                        })
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"没找到该文件的位置！"
                            }
                        })
                    }
                }
            });
            connection.release();
        }
    });
});


module.exports = router;